﻿
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections
Imports System
Imports System.Data.OleDb
Namespace SPDAO

    Public Class CSPdataprovider
        Protected strconnectionstring As String = "Data Source=(local);Initial Catalog=quanlycanho;Integrated Security=True"
        Protected connection As SqlConnection
        Protected adapter As SqlDataAdapter
        Protected command As SqlCommand
        Protected Sub connect()
            connection = New SqlConnection(strconnectionstring)
            connection.Open()
        End Sub
        Protected Sub disconnect()
            connection.Close()
        End Sub
        Protected Sub Executenonquery(ByVal sql As String)
            command = New SqlCommand(sql)
            command.CommandType = CommandType.Text
            command.ExecuteNonQuery()
        End Sub
        Protected Sub excuteupdatequery(ByVal sql As String)
            connect()
            Executenonquery(sql)
            disconnect()
        End Sub
        Public Function ExcuteQuery(ByVal strSelect As String) As DataSet
            Dim dataset As New DataSet()
            command = New SqlCommand()
            command.Connection = Me.connection
            adapter = New SqlDataAdapter(strSelect, connection)
            Try
                adapter.Fill(dataset)
            Catch e As SqlException

                System.Windows.Forms.MessageBox.Show(e.ToString())

            End Try
            Return dataset

        End Function
        Public Function Excutenonquery_Datatable(ByVal strSelect As String) As DataTable
            Return ExcuteQuery(strSelect).Tables(0)
        End Function
        Protected Overridable Function GetInfrom1row(ByVal dt As DataTable, ByVal i As Integer) As Object
            Return Nothing
        End Function
        Protected Function convertdatatable2arraylist(ByVal dt As DataTable) As ArrayList
            Dim ar As New ArrayList()
            Dim i As Integer, n As Integer = dt.Rows.Count
            For i = 0 To n - 1
                Dim dto As Object = GetInfrom1row(dt, i)
                ar.Add(dto)

            Next
            Return ar
        End Function
        Protected Function createIdCode(ByVal strid As String, ByVal field As String, ByVal table As String, ByVal length As Integer) As String
            Try
                Me.connect()
                Dim idcode As String = strid & "000000000"
                Dim query As String = "select max(" & field.Trim() & ") from" & table.Trim() & "whereleft(" & field.Trim() & "," + strid.Length.ToString() & ")='" & strid.Trim() & ""
                Me.command = New SqlCommand(query, Me.connection)
                Me.command.CommandType = CommandType.Text
                Dim reader As SqlDataReader = Me.command.ExecuteReader()
                While reader.Read()
                    If Not reader.IsDBNull(0) Then
                        idcode = Convert.ToString(reader.GetValue(0))

                    End If

                End While
                idcode = idcode.Substring(strid.Length)
                idcode = "0000000000000000" & Convert.ToString(Convert.ToUInt64(idcode) + 1)
                idcode = strid + idcode.Substring(idcode.Length - length + strid.Length)
                Me.disconnect()
                Return idcode
            Catch ex As Exception
                Me.disconnect()
                Throw ex

            End Try
        End Function
    End Class

End Namespace